package com.my.easypoi.test.simple;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.io.FileInputStream;
import java.util.Arrays;
import java.util.LinkedHashMap;

@Slf4j
public class Excel {
//    private final static Logger logger = LoggerFactory.getLogger(ExcelTranslator.class);

    private final static ObjectMapper mapper = new ObjectMapper();
    /*读取excel的值为json*/
    public static void main(String[] args) {
        try {
            //最终返回的json对象
            JSONObject rowObj = new JSONObject(new LinkedHashMap());

            FileInputStream inp = new FileInputStream("D:\\timo\\aaa.xlsx");
            Workbook workbook = WorkbookFactory.create(inp);
            Sheet sheettimo = workbook.getSheetAt(0);//要读取页
            int rownum11s= sheettimo.getPhysicalNumberOfRows();//总行数
            String stringCellValueArry = "";//json 的key 如果是array类型 将注释也放在key中

            JSONArray jsonArray = new JSONArray();//array类型 子集存放位置
            JSONObject sunrowObj = new JSONObject(new LinkedHashMap());//array类型中的每一个json对象存放

            for(int a = 2; a < rownum11s ; a++){
                Row row1 = sheettimo.getRow(a);//获取第a行

                Cell cellData5 = row1.getCell(5);//宽表注释
                Cell cellData6 = row1.getCell(6);//字段解释说明

                Cell cellData = row1.getCell(2);
                String stringCellValue1 = cellData.getStringCellValue();//key 宽表字段

                Cell cellData3 = row1.getCell(3);
                String stringCellValue = cellData3.getStringCellValue();//key类型 宽表字段类型

                String val = stringCellValue+":::"+cellData5.getStringCellValue()+":::"+cellData6.getStringCellValue();

                short getFillForegroundColor = cellData3.getCellStyle()
                        .getFillForegroundColor();//前景色
                CellStyle cellStyle = cellData3.getCellStyle();
//                Color fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
                Color fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
                String argbHex = "";
                if(fillForegroundColorColor!=null){
//                    HSSFColor hssfColor = HSSFColor.toHSSFColor(fillForegroundColorColor);
//                    String hexString = hssfColor.getHexString();
                    XSSFColor xssfColor = XSSFColor.toXSSFColor(fillForegroundColorColor);
                    argbHex = xssfColor.getARGBHex();
                    if("FFC4C4C4".equals(argbHex)){
                        //灰色
                        continue;
                    }
                    if("FFFFFF00".equals(argbHex)){
                        //黄
                    }
                }
//                short getFillBackgroundColor = cellData3.getCellStyle()
//                        .getFillBackgroundColor();//背景色
                if(null == stringCellValue1 || "".equals(stringCellValue1)){
                    //代表是子集中的json
                    Cell cellData4 = row1.getCell(4);
                    String stringCellValue4 = cellData4.getStringCellValue();
                    sunrowObj.put(stringCellValue4,val);
                }else if("Array".equals(stringCellValue) || "FFFF0000".equals(argbHex)){
                    //子集处理
                    JSONObject sunrowObj2 = new JSONObject(sunrowObj);
                    jsonArray.add(sunrowObj2);
                    sunrowObj = new JSONObject(new LinkedHashMap());
                    JSONArray jsonArray2 = new JSONArray(Arrays.asList(jsonArray.toArray()));
                    if(!"".equals(stringCellValueArry)){
                        rowObj.put(stringCellValueArry,jsonArray2);//存储的是上一个arry
                    }
                    jsonArray = new JSONArray();//清空
                    stringCellValueArry = stringCellValue1+":::"+cellData5.getStringCellValue();
                }else {

                    rowObj.put(stringCellValue1,val);
                }
            }
            //最后一个集合单独加
            jsonArray.add(sunrowObj);
            if(!"".equals(stringCellValueArry)){
                rowObj.put(stringCellValueArry,jsonArray);
            }

            //获取sheet数
            int sheetNum = workbook.getNumberOfSheets();

//            for (int s = 0; s < sheetNum; s++) {
//                // Get the Sheet of s.
//                Sheet sheet = workbook.getSheetAt(s);
//                //获取最大行数
//                int rownum = sheet.getPhysicalNumberOfRows();
//                if (rownum <= 1) {
//                    continue;
//                }
//                //获取第一行
//                Row row1 = sheet.getRow(0);
//                //获取最大列数
//                int colnum = row1.getPhysicalNumberOfCells();
//                JSONArray jsonArray = new JSONArray();
//                for (int i = 1; i < rownum; i++) {
//                    Row row = sheet.getRow(i);
//                    if (row != null) {
////                    List<Object> list = new ArrayList<>();
//                        JSONObject rowObj = new JSONObject();
//                        //循环列
//                        for (int j = 0; j < colnum; j++) {
//                            Cell cellData = row.getCell(j);
//                            if (cellData != null) {
//                                //判断cell类型
//                                switch (cellData.getCellType()) {
//                                    case Cell.CELL_TYPE_NUMERIC: {
//                                        rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
//                                        break;
//                                    }
//                                    case Cell.CELL_TYPE_FORMULA: {
//                                        //判断cell是否为日期格式
//                                        if (DateUtil.isCellDateFormatted(cellData)) {
//                                            //转换为日期格式YYYY-mm-dd
//                                            rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getDateCellValue());
//                                        } else {
//                                            //数字
//                                            rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
//                                        }
//                                        break;
//                                    }
//                                    case Cell.CELL_TYPE_STRING: {
//                                        rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getStringCellValue());
//                                        break;
//                                    }
//                                    default:
//                                        rowObj.put(row1.getCell(j).getStringCellValue(), "");
//                                }
//                            } else {
//                                rowObj.put(row1.getCell(j).getStringCellValue(), "");
//
//                            }
//                        }
//                        jsonArray.add(rowObj);
//                    }
//                }
//                logger.info(jsonArray.toJSONString());
//                jsonObject.put(sheet.getSheetName(), jsonArray);
//            }
//            logger.info(jsonObject.toJSONString());
//            logger.info(rowObj.toJSONString());
            log.info(rowObj.toJSONString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



}